﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lab_Report_Search_Tool
{
    public class dbConnection
    {
        private static bool isTest = false;
        private static bool isLogging = true;
        private static MySql.Data.MySqlClient.MySqlConnection testConn;
        private static SqlConnection conn;
         
        static private SqlConnection getConn()
        {
            return new SqlConnection("Server=SQL2012;Database=Datawarehouse;User Id=bdeyoungApplicationsReadOnly;Password=5QIpk9ijpIHHHJqjxL45;");
        }
        
        static public string[][] getFields()
        {
            if (isTest)
            {
                return getFields_test();
            }
            List<String[]> ret = new List<String[]>();

            using (conn = getConn())
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("get_LRST_fields", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                //cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        var colVal = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            colVal.Add(rdr.GetString(i)); 
                            Console.WriteLine(rdr.GetString(i)); 
                        }
                        ret.Add(colVal.ToArray());
                    }
                }
            }

            return ret.ToArray();
        }

        static public string[][] getFilters()
        {
            if (isTest)
            {
                return getFilters_test();
            }
             List<String[]> ret = new List<String[]>();

             using (conn = getConn())
             {
                 conn.Open();

                 // 1.  create a command object identifying the stored procedure
                 SqlCommand cmd = new SqlCommand("get_LRST_filters", conn);

                 // 2. set the command object so it knows to execute a stored procedure
                 cmd.CommandType = CommandType.StoredProcedure;

                 // 3. add parameter to command, which will be passed to the stored procedure
                 //cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

                 // execute the command
                 using (SqlDataReader rdr = cmd.ExecuteReader())
                 {
                     // iterate through results, printing each to console
                     while (rdr.Read())
                     {
                         var colVal = new List<string>();
                         for (int i = 0; i < rdr.FieldCount; i++)
                         {
                             colVal.Add(rdr.GetString(i));
                             Console.WriteLine(rdr.GetString(i));
                         }
                         ret.Add(colVal.ToArray());
                     }
                 }
                 return ret.ToArray();
             }
        }

        static public string getList(string fieldName)
        {
            if (isTest)
            {
                return getList_test();
            }

            string csv = "";

            using (conn = getConn())
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("get_LRST_filters_choices", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@displayName", fieldName));
                Console.WriteLine("fieldName" + fieldName);
                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    Console.WriteLine(rdr.HasRows + " it has rows? ");
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    { 
                        Console.WriteLine(rdr.GetString(0));
                        csv += rdr.GetString(0) + "^";
                        
                    }
                }

                return csv;
            }
        }

        static public string[][] getData(int includeDrugs, string whereClause, int mostrecent)
        {
            if (isTest)
            {
                return getData_test(includeDrugs,whereClause,mostrecent);
            }
            if (isLogging)
            {
                Log_Query(includeDrugs, whereClause.Replace("\'\'", "\'"), mostrecent);
            }
            List<String[]> ret = new List<String[]>();

            using (conn = getConn())
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("get_LRST_Data", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@includeDrugs", includeDrugs));
                cmd.Parameters.Add(new SqlParameter("@whereClause", whereClause.Replace("\'\'","\'")));
                cmd.Parameters.Add(new SqlParameter("@mostrecent", mostrecent));

                Console.WriteLine(whereClause.Replace("\'\'", "\'")); 
                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    var colVal = new List<string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        colVal.Add(rdr.GetName(i));
                        
                    }
                    ret.Add(colVal.ToArray());
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        colVal = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            colVal.Add(rdr.GetString(i));
                            //Console.WriteLine(rdr.GetString(i));
                        }
                        ret.Add(colVal.ToArray());
                    }
                }
                return ret.ToArray();
            }
        }

        static public void Log_Query(int includeDrugs, string whereClause, int mostrecent)
        {
            if (isTest)
            {
                Log_Query_Test(includeDrugs, whereClause, mostrecent);
                return;
            }
             
            using (conn = getConn())
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("log_LRST_Query", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@userName", Environment.UserName));
                cmd.Parameters.Add(new SqlParameter("@ColumnCSV",""));
                cmd.Parameters.Add(new SqlParameter("@includeDrugs", includeDrugs));
                cmd.Parameters.Add(new SqlParameter("@whereClause", whereClause.Replace("\'\'", "\'")));
                cmd.Parameters.Add(new SqlParameter("@MostRecentReports", mostrecent));
                 
                // execute the command
                cmd.ExecuteReader();
                 
                return;
            }
        }

        
        #region Test_db

        private static MySql.Data.MySqlClient.MySqlConnection getConn_Test()
        {
            string strConnection = "host=db4free.net;user=dbadmin;password=dbadmin;database=datawarehouse490;";
            testConn = new MySql.Data.MySqlClient.MySqlConnection();
            testConn.ConnectionString = strConnection;

            return testConn;
        }

        private static string[][] getFields_test()
        {
            List<String[]> ret = new List<String[]>();

            using (testConn = getConn_Test())
            {
                testConn.Open();

                // 1.  create a command object identifying the stored procedure
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("get_LRST_fields", testConn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                //cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

                // execute the command
                using (MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        var colVal = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            colVal.Add(rdr.GetString(i));
                            Console.WriteLine(rdr.GetString(i));
                        }
                        ret.Add(colVal.ToArray());
                    }
                }
                return ret.ToArray();
            }
        }

        private static string[][] getFilters_test()
        {
            List<String[]> ret = new List<String[]>();

            using (testConn = getConn_Test())
            {
                testConn.Open();

                // 1.  create a command object identifying the stored procedure
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("get_LRST_filters", testConn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                //cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

                // execute the command
                using (MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        var colVal = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            colVal.Add(rdr.GetString(i));
                            Console.WriteLine(rdr.GetString(i));
                        }
                        ret.Add(colVal.ToArray());
                    }
                }
                return ret.ToArray();
            }
        }

        private static string getList_test()
        {
            return "test1^test2^test3";
        }

        private static string[][] getData_test(int includeDrugs, string whereClause, int mostrecent)
        {
            if (isLogging)
            {
                Log_Query_Test(includeDrugs, whereClause.Replace("\'\'", "\'"), mostrecent);
            }

          List<String[]> ret = new List<String[]>();

            using (testConn = getConn_Test())
            {
                testConn.Open();

                // 1.  create a command object identifying the stored procedure
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("get_LRST_Data", testConn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;
                 
                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@includeDrugs", includeDrugs));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@whereClause", whereClause.Replace("\'\'","\'")));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@mostrecent", mostrecent));

                Console.WriteLine(whereClause.Replace("\'\'", "\'")); 
                // execute the command
                using (MySql.Data.MySqlClient.MySqlDataReader  rdr = cmd.ExecuteReader())
                {
                    var colVal = new List<string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        colVal.Add(rdr.GetName(i));
                        
                    }
                    ret.Add(colVal.ToArray());
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        colVal = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            colVal.Add(rdr.GetString(i));
                            //Console.WriteLine(rdr.GetString(i));
                        }
                        ret.Add(colVal.ToArray());
                    }
                }
                return ret.ToArray();
            }
        }

        private static void Log_Query_Test(int includeDrugs, string whereClause, int mostrecent)
        {
            using (testConn = getConn_Test())
            {
                testConn.Open();

                // 1.  create a command object identifying the stored procedure
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("log_LRST_Query", testConn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@userName", Environment.UserName));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@ColumnCSV", ""));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@includeDrugs", includeDrugs));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@whereClause", whereClause.Replace("\'\'", "\'")));
                cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@mostrecentReports", mostrecent));

                // execute the command
                //cmd.ExecuteReader();

                return;
            }
        }

        #endregion Test_db
        }

    }
 
 
